package cn.springboot.osbulkparts.service.impl;

import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import cn.springboot.osbulkparts.common.utils.ExcelCopySheetUtil;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.security.core.Authentication;
import org.springframework.stereotype.Service;
import org.springframework.web.util.UriUtils;

import com.github.pagehelper.util.StringUtil;

import cn.springboot.osbulkparts.common.CommonBusinessException;
import cn.springboot.osbulkparts.common.security.entity.SecurityUserInfoEntity;
import cn.springboot.osbulkparts.common.utils.CommonMethods;
import cn.springboot.osbulkparts.config.i18n.I18nMessageBean;
import cn.springboot.osbulkparts.dao.basedata.MEnterpriseInfoDao;
import cn.springboot.osbulkparts.dao.report.ReportOrderDetailInfoDao;
import cn.springboot.osbulkparts.dao.user.MRoleInfoDao;
import cn.springboot.osbulkparts.dao.warehouse.MPackingDetailsInfoDao;
import cn.springboot.osbulkparts.entity.MEnterpriseInfoEntity;
import cn.springboot.osbulkparts.entity.MPackingDetailsInfoEntity;
import cn.springboot.osbulkparts.entity.MRoleInfoEntity;
import cn.springboot.osbulkparts.entity.ReportOrderDetailInfoEntity;
import cn.springboot.osbulkparts.entity.TOrderDetailInfoEntity;
import cn.springboot.osbulkparts.service.ReportOrderDetailService;

@Service
public class ReportOrderDetailServiceImpl implements ReportOrderDetailService{

    @Autowired
    private I18nMessageBean messageBean;
    @Autowired
    private ReportOrderDetailInfoDao reportDao;
    @Autowired
    private MPackingDetailsInfoDao packingReportDao;
    @Autowired
    private MEnterpriseInfoDao menterpriseInfoDao;
    @Autowired
    private MRoleInfoDao mroleInfoDao;
    
	@Override
	public ResponseEntity<byte[]> DownloadReportOrderDetail(TOrderDetailInfoEntity tOrderDetailInfoEntity, Locale locale, Authentication auth) {
		messageBean.setLocale(null,null,locale);
		SecurityUserInfoEntity principal = (SecurityUserInfoEntity)auth.getPrincipal();
		MRoleInfoEntity roleInfoEntity = mroleInfoDao.selectRoleInfo(principal.getRoleIdSelected(),tOrderDetailInfoEntity.getLanguageFlag());
        if(principal.getUserType()!=1) {
        	tOrderDetailInfoEntity.setDataRoleAt(roleInfoEntity.getRoleAt());
        }
		String[] title = messageBean.getMessage("file.title.report.orderDetail").split(",");
		List<ReportOrderDetailInfoEntity> resultList = reportDao.getReportOrderDetailInfo(tOrderDetailInfoEntity);
		List<ReportOrderDetailInfoEntity> resultDateList = reportDao.getOrderDate(tOrderDetailInfoEntity);
		ResponseEntity<byte[]> result = educeExcel(title,resultList,resultDateList,locale);
		return result;
	}
	
	@Override
	public ResponseEntity<byte[]> ExportSubBoxReport(MPackingDetailsInfoEntity mPackingDetailsInfoEntity,Locale locale,Authentication auth){
		messageBean.setLocale(null, null, locale);
		ResponseEntity<byte[]> result = null;
		SecurityUserInfoEntity principal = (SecurityUserInfoEntity)auth.getPrincipal();
		MRoleInfoEntity roleInfoEntity = mroleInfoDao.selectRoleInfo(principal.getRoleIdSelected(),mPackingDetailsInfoEntity.getLanguageFlag());
		List<MPackingDetailsInfoEntity> resultList = packingReportDao.selectSubBoxReport(mPackingDetailsInfoEntity);
		List<MPackingDetailsInfoEntity> resultTotalList = packingReportDao.selectTotalSubBoxReport(mPackingDetailsInfoEntity);
		List<MPackingDetailsInfoEntity> resultSumList = packingReportDao.selectSumSubBoxReport(mPackingDetailsInfoEntity);
		MEnterpriseInfoEntity enterSearchKey = new MEnterpriseInfoEntity();
		List<MEnterpriseInfoEntity> resultEnterpriseList = menterpriseInfoDao.selectList(enterSearchKey);
		if((resultList != null && resultList.size()>0)
			&& (resultTotalList != null && resultTotalList.size()>0)
			&& (resultSumList != null && resultSumList.size()>0)) {
			result = subBoxEduceExcel(resultList,resultTotalList,resultSumList,resultEnterpriseList, locale);
		}
		return result;
	}
	
	@Override
	public ResponseEntity<byte[]> ExportPackingListReport(MPackingDetailsInfoEntity mPackingDetailsInfoEntity,Locale locale,Authentication auth){
		messageBean.setLocale(null, null, locale);
		ResponseEntity<byte[]> result = null;
		SecurityUserInfoEntity principal = (SecurityUserInfoEntity)auth.getPrincipal();
		MRoleInfoEntity roleInfoEntity = mroleInfoDao.selectRoleInfo(principal.getRoleIdSelected(),mPackingDetailsInfoEntity.getLanguageFlag());
		List<MPackingDetailsInfoEntity> resultList = packingReportDao.selectPackingList(mPackingDetailsInfoEntity);
		MEnterpriseInfoEntity enterSearchKey = new MEnterpriseInfoEntity();
		List<MEnterpriseInfoEntity> resultEnterpriseList = menterpriseInfoDao.selectList(enterSearchKey);
		if(resultList != null && resultList.size()>0) {
			result = packingListExcel(resultList,resultEnterpriseList, locale);
		}
		return result;
	}

	@Override
	public ResponseEntity<byte[]> ExportattachmentListReport(MPackingDetailsInfoEntity mPackingDetailsInfoEntity, Locale locale, Authentication auth) {
		messageBean.setLocale(null, null, locale);
		ResponseEntity<byte[]> result = null;
		SecurityUserInfoEntity principal = (SecurityUserInfoEntity)auth.getPrincipal();
		MRoleInfoEntity roleInfoEntity = mroleInfoDao.selectRoleInfo(principal.getRoleIdSelected(),mPackingDetailsInfoEntity.getLanguageFlag());
		List<MPackingDetailsInfoEntity> resultList = packingReportDao.selectAttachList(mPackingDetailsInfoEntity);
		if(resultList != null && resultList.size()>0) {
			result = attachmentListExcel(resultList, locale);
		}
		return result;
	}
	
	@Override
	public ResponseEntity<byte[]> insuranceReport(MPackingDetailsInfoEntity mPackingDetailsInfoEntity, Locale locale, Authentication auth) {
		messageBean.setLocale(null, null, locale);
		ResponseEntity<byte[]> result = null;
		SecurityUserInfoEntity principal = (SecurityUserInfoEntity)auth.getPrincipal();
		MRoleInfoEntity roleInfoEntity = mroleInfoDao.selectRoleInfo(principal.getRoleIdSelected(),mPackingDetailsInfoEntity.getLanguageFlag());
		List<MPackingDetailsInfoEntity> resultList = packingReportDao.selectPackingListByMaterialCode(mPackingDetailsInfoEntity);
		MEnterpriseInfoEntity enterSearchKey = new MEnterpriseInfoEntity();
		List<MEnterpriseInfoEntity> resultEnterpriseList = menterpriseInfoDao.selectList(enterSearchKey);
		if(resultList != null && resultList.size()>0) {
			result = insuranceReport(resultList,resultEnterpriseList, locale);
		}
		return result;
	}
	
	@Override
	public ResponseEntity<byte[]> ExportBookingListReport(MPackingDetailsInfoEntity mPackingDetailsInfoEntity, Locale locale, Authentication auth) {
		messageBean.setLocale(null, null, locale);
		ResponseEntity<byte[]> result = null;
		SecurityUserInfoEntity principal = (SecurityUserInfoEntity)auth.getPrincipal();
		MRoleInfoEntity roleInfoEntity = mroleInfoDao.selectRoleInfo(principal.getRoleIdSelected(),mPackingDetailsInfoEntity.getLanguageFlag());
		List<MPackingDetailsInfoEntity> resultList = packingReportDao.selectBookingList(mPackingDetailsInfoEntity);
		MEnterpriseInfoEntity enterSearchKey = new MEnterpriseInfoEntity();
		List<MEnterpriseInfoEntity> resultEnterpriseList = menterpriseInfoDao.selectList(enterSearchKey);
		if(resultList != null && resultList.size()>0) {
			result = bookingListReport(resultList,resultEnterpriseList, locale);
		}
		return result;
	}
	//提单样本（铁路）  billOfLadingSample.xlsx
	@Override
	public ResponseEntity<byte[]> billOfLadingSampleReport(MPackingDetailsInfoEntity mPackingDetailsInfoEntity, Locale locale, Authentication auth) {
		messageBean.setLocale(null, null, locale);
		ResponseEntity<byte[]> result = null;
		SecurityUserInfoEntity principal = (SecurityUserInfoEntity)auth.getPrincipal();
		MRoleInfoEntity roleInfoEntity = mroleInfoDao.selectRoleInfo(principal.getRoleIdSelected(),mPackingDetailsInfoEntity.getLanguageFlag());
		List<MPackingDetailsInfoEntity> resultList = packingReportDao.selectPackingList4BillOfLadingSample(mPackingDetailsInfoEntity);
		MEnterpriseInfoEntity enterSearchKey = new MEnterpriseInfoEntity();
		List<MEnterpriseInfoEntity> resultEnterpriseList = menterpriseInfoDao.selectList(enterSearchKey);
		if(resultList != null && resultList.size()>0) {
			result = billOfLadingSampleReportExcel(resultList,resultEnterpriseList, locale);
		}
		return result;
	}

	/****Private Methods****/
	/**
	 * 订单详情报表导出
	 * @param titles 第一列名
	 * @param list 向单元格插入数据
	 * @return
	 */
	private ResponseEntity<byte[]> educeExcel(String[] titles,List<ReportOrderDetailInfoEntity> list, List<ReportOrderDetailInfoEntity> dateList,Locale locale){
		messageBean.setLocale(null,null,locale);
		ResponseEntity<byte[]> response = null;
		//创建Excel对象
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		HSSFWorkbook workbook = new HSSFWorkbook();
		//输出Excel文件  
		try {
			//创建工作表单
			HSSFSheet sheet = workbook.createSheet(messageBean.getMessage("file.name.orderDetail"));  
			//创建HSSFRow对象 （行）第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
			HSSFRow row = sheet.createRow(0); 	
			row.setHeightInPoints(20);// 设备标题的高度
			//创建HSSFCell对象  （单元格）
			HSSFCell cell=null; 
			//设置第一列单元格的列
			int m = 0;
			for(int i = 0; i < titles.length; i++){
				if(i==6) {
					for(int j=1;j<=dateList.size();j++) {
						cell = row.createCell(m);//列索引从0开始
						String orderDateStr = dateList.get(j-1).getOrderDate().substring(0,4)+"年"+
						dateList.get(j-1).getOrderDate().substring(4,6)+"月"+
								dateList.get(j-1).getOrderDate().substring(6,8)+"日";
						cell.setCellValue(orderDateStr);//列名1
						m = m+1;
					}
				}else {
					cell = row.createCell(m);//列索引从0开始
					cell.setCellValue(titles[i]);//列名1
					m = m+1;
				}
			};
			
			CellStyle  style =  workbook.createCellStyle();
			style.setFillPattern(FillPatternType.SOLID_FOREGROUND);  
			style.setFillForegroundColor(IndexedColors.RED.getIndex());     
			CellStyle textStyle = workbook.createCellStyle();
			DataFormat format = workbook.createDataFormat();
			textStyle.setDataFormat(format.getFormat("@"));
			int n = 0;
			BigDecimal amountTotal = new BigDecimal("0");

			//设置单元格的值  
			for (int i = 0; i < list.size(); i++) {
				
				ReportOrderDetailInfoEntity example = list.get(i);
				if(i>0) {
					if(!example.getMaterialCode().equals(list.get(i-1).getMaterialCode())) {
						n = n+1;
						amountTotal = new BigDecimal("0");
						row = sheet.createRow(n+1);
					}
				}else {
					row = sheet.createRow(n+1);
				}
				//物料专用号
				row.createCell(0).setCellValue(example.getMaterialCode());
				//物料中文描述
				row.createCell(1).setCellValue(example.getMaterialDescCn());
				//物料英文描述
				row.createCell(2).setCellValue(example.getMaterialDescEn());
				//物料俄文描述
				row.createCell(3).setCellValue(example.getMaterialDescRn());
				//供应商中文名称
				if(example.getMSupplierInfoEntity() != null) {
					row.createCell(4).setCellValue(example.getMSupplierInfoEntity().getSupplierNameCn());
				}
				
				//最小装箱量
				row.createCell(5).setCellType(CellType.NUMERIC);
				if(example.getMaterialMinpackageAmt()!=null) {
					row.createCell(5).setCellValue(Double.parseDouble(example.getMaterialMinpackageAmt().toString()));
				}
				
				//订单日期
				String orderDate = example.getOrderDate();
				//订单数量
				int nextI = 0;
				for(int j=0;j<dateList.size();j++) {
					if(orderDate.equals(dateList.get(j).getOrderDate())) {
						row.createCell(j+6).setCellType(CellType.NUMERIC);
						row.createCell(j+6).setCellValue(example.getAmountTotal()!=null?Double.parseDouble(example.getAmountTotal().toString()):null);
						nextI = j+6;
						if(example.getAmountTotal() != null) {
							amountTotal = amountTotal.add(CommonMethods.changeToBigdecimal(example.getAmountTotal().toString()));
						}else {
							amountTotal = amountTotal.add(BigDecimal.ZERO);
						}
					}
				}
				//总计
				row.createCell(dateList.size()+6).setCellType(CellType.NUMERIC);
				row.createCell(dateList.size()+6).setCellValue(Double.parseDouble(amountTotal.toString()));
				//单位
				if(example.getDictMaterialUnit() != null) {
					row.createCell(dateList.size()+7).setCellValue(example.getDictMaterialUnit().getName()!=null?example.getDictMaterialUnit().getName():null);
				}
				else {
					row.createCell(dateList.size()+7).setCellValue("");
				}
				//换算后单位
//				row.createCell(9).setCellValue(example.getDictRelationUnit()!=null?example.getDictRelationUnit().getName():"");
				//换算后数量
//				row.createCell(10).setCellValue(example.getMaterialRelationQuantity()!=null?example.getMaterialRelationQuantity().toString():"");
			}
			workbook.write(os);
			workbook.close();
			String filename_enc = UriUtils.encode(messageBean.getMessage("file.name.orderDetail"), "UTF-8");
			response = ResponseEntity.ok()
				.contentType(MediaType.parseMediaType("application/octet-stream"))
				.header("Access-Control-Expose-Headers","Content-Disposition")
				.header("Content-Disposition","attachment; filename*=UTF-8''" + filename_enc+".xls")
				.body(os.toByteArray());
			return response;
		} catch (Exception e) {
			throw new CommonBusinessException(e.getMessage().toString());
		}
	}
	
	/**
	 * 分箱明细报表导出
	 * @param titles 第一列名
	 * @param list 向单元格插入数据
	 * @return
	*/
	private ResponseEntity<byte[]> subBoxEduceExcel(
			List<MPackingDetailsInfoEntity> list,
			List<MPackingDetailsInfoEntity> listTotal,
			List<MPackingDetailsInfoEntity> listSum,
			List<MEnterpriseInfoEntity> listEnterprise,
			Locale locale){
		messageBean.setLocale(null,null,locale);
		ResponseEntity<byte[]> response = null;
		try {
			// 读取模板
	        ClassPathResource classPathResource = new ClassPathResource("template/packingDetail/"+"subboxTemp.xlsx");
	        InputStream inputStream= classPathResource.getInputStream();
			//创建Excel对象
			ByteArrayOutputStream os = new ByteArrayOutputStream();
			HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
			// 明细数据样式
			HSSFCellStyle setBorder = workbook.createCellStyle();
			// 设置边框
			setBorder.setBorderTop(BorderStyle.THIN);
			setBorder.setBorderBottom(BorderStyle.THIN);
			setBorder.setBorderLeft(BorderStyle.THIN);
			setBorder.setBorderRight(BorderStyle.THIN);
			
			// 明细数据数值样式
			HSSFCellStyle setBorderNum = workbook.createCellStyle();
			// 设置边框
			setBorderNum.setBorderTop(BorderStyle.THIN);
			setBorderNum.setBorderBottom(BorderStyle.THIN);
			setBorderNum.setBorderLeft(BorderStyle.THIN);
			setBorderNum.setBorderRight(BorderStyle.THIN);
			setBorderNum.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.000000"));
			
			/*********分票明细sheet*********/
			HSSFSheet firstSheet = workbook.getSheetAt(0);
			
			// 主提单号
			HSSFRow mainRowBillNo = firstSheet.getRow(2);
			mainRowBillNo.getCell(2).setCellValue(list.get(0).getBillNo());
			// 航名/航次
			HSSFRow mainRowVessel = firstSheet.getRow(3);
			mainRowVessel.getCell(2).setCellValue(list.get(0).getVesselNameVoyage());
			
			for(int i = 0;i<list.size();i++) {
				HSSFRow row = firstSheet.createRow(i+5);
				MPackingDetailsInfoEntity packinigDetail = list.get(i);
				BigDecimal amountTotal = new BigDecimal("0");
				BigDecimal weight = new BigDecimal(packinigDetail.getGrossWeight());
				BigDecimal conWeight = new BigDecimal(packinigDetail.getContainerWeight());
				amountTotal = weight.add(conWeight);
				
				row.createCell(1).setCellValue(packinigDetail.getSecondaryBillNo());
				row.getCell(1).setCellStyle(setBorder);
				row.createCell(2).setCellValue(packinigDetail.getCaseNumber());
				row.getCell(2).setCellStyle(setBorder);
				row.createCell(3).setCellValue(packinigDetail.getLeadMark());
				row.getCell(3).setCellStyle(setBorder);
				row.createCell(4).setCellValue(packinigDetail.getContainerSize());
				row.getCell(4).setCellStyle(setBorder);
				row.createCell(5).setCellType(CellType.NUMERIC);
				row.createCell(5).setCellValue(Double.parseDouble(packinigDetail.getNumberOfPackages()));
				row.getCell(5).setCellStyle(setBorderNum);
				row.createCell(6).setCellType(CellType.NUMERIC);
				row.createCell(6).setCellValue(Double.parseDouble(packinigDetail.getGrossWeight()));
				row.getCell(6).setCellStyle(setBorderNum);
				row.createCell(7).setCellType(CellType.NUMERIC);
				row.createCell(7).setCellValue(Double.parseDouble(packinigDetail.getSize()));
				row.getCell(7).setCellStyle(setBorderNum);
				row.createCell(8).setCellValue(packinigDetail.getUnit());
				row.getCell(8).setCellStyle(setBorder);
				row.createCell(9).setCellType(CellType.NUMERIC);
				row.createCell(9).setCellValue(Double.parseDouble(amountTotal.toString()));
				row.getCell(9).setCellStyle(setBorderNum);
				row.createCell(10).setCellType(CellType.NUMERIC);
				row.createCell(10).setCellValue(Double.parseDouble(packinigDetail.getContainerWeight().toString()));
				row.getCell(10).setCellStyle(setBorderNum);
			}
			
			/*********分票明细sheet*********/
			
			/*********分箱统计sheet*********/
			HSSFSheet secondSheet = workbook.getSheetAt(1);
			for(int i = 0;i<listTotal.size();i++) {
				HSSFRow row = secondSheet.createRow(i+2);
				MPackingDetailsInfoEntity packinigDetailTotal = listTotal.get(i);
				BigDecimal amountTotal = new BigDecimal("0");
				BigDecimal weight = new BigDecimal(packinigDetailTotal.getGrossWeight());
				BigDecimal conWeight = new BigDecimal(packinigDetailTotal.getContainerWeight());
				amountTotal = weight.add(conWeight);
				
				row.createCell(1).setCellValue(packinigDetailTotal.getCaseNumber());
				row.getCell(1).setCellStyle(setBorder);
				row.createCell(2).setCellValue(packinigDetailTotal.getLeadMark());
				row.getCell(2).setCellStyle(setBorder);
				row.createCell(3).setCellType(CellType.NUMERIC);
				row.createCell(3).setCellValue(Double.parseDouble(packinigDetailTotal.getNumberOfPackages()));
				row.getCell(3).setCellStyle(setBorderNum);
				row.createCell(4).setCellType(CellType.NUMERIC);
				row.createCell(4).setCellValue(Double.parseDouble(weight.toString()));
				row.getCell(4).setCellStyle(setBorderNum);
				row.createCell(5).setCellType(CellType.NUMERIC);
				row.createCell(5).setCellValue(Double.parseDouble(packinigDetailTotal.getSize().toString()));
				row.getCell(5).setCellStyle(setBorderNum);
				row.createCell(6).setCellType(CellType.NUMERIC);
				row.createCell(6).setCellValue(Double.parseDouble(conWeight.toString()));
				row.getCell(6).setCellStyle(setBorderNum);
				row.createCell(7).setCellType(CellType.NUMERIC);
				row.createCell(7).setCellValue(Double.parseDouble(amountTotal.toString()));
				row.getCell(7).setCellStyle(setBorderNum);
			}
			/*********分箱统计sheet*********/
			
			/*********分箱明细sheet*********/
			HSSFSheet thirdSheet = workbook.getSheetAt(2);
			for(int i = 0;i<listSum.size();i++) {
				HSSFRow row = thirdSheet.createRow(i+2);
				MPackingDetailsInfoEntity packinigDetailTotal = listSum.get(i);
				
				row.createCell(1).setCellValue(packinigDetailTotal.getCaseNumber());
				row.getCell(1).setCellStyle(setBorder);
				row.createCell(2).setCellValue(packinigDetailTotal.getHsNo());
				row.getCell(2).setCellStyle(setBorder);
				row.createCell(3).setCellValue(packinigDetailTotal.getMaterialNameEn());
				row.getCell(3).setCellStyle(setBorder);
				row.createCell(4).setCellType(CellType.NUMERIC);
				row.createCell(4).setCellValue(Double.parseDouble(packinigDetailTotal.getNumberOfPackages()));
				row.getCell(4).setCellStyle(setBorderNum);
				row.createCell(5).setCellType(CellType.NUMERIC);
				row.createCell(5).setCellValue(Double.parseDouble(packinigDetailTotal.getGrossWeight().toString()));
				row.getCell(5).setCellStyle(setBorderNum);
				row.createCell(6).setCellType(CellType.NUMERIC);
				row.createCell(6).setCellValue(Double.parseDouble(packinigDetailTotal.getSize().toString()));
				row.getCell(6).setCellStyle(setBorderNum);
			}
			/*********分箱明细sheet*********/
			/*********企业明细sheet*********/
			HSSFSheet forthSheet = workbook.getSheetAt(3);
			for(int i = 0;i<listEnterprise.size();i++) {
				HSSFRow row = forthSheet.createRow(i+2);
				MEnterpriseInfoEntity menterpriseInfoEntity = listEnterprise.get(i);
				
				row.createCell(1).setCellValue(menterpriseInfoEntity.getNameEn());
				row.getCell(1).setCellStyle(setBorder);
				row.createCell(2).setCellValue(menterpriseInfoEntity.getAddress());
				row.getCell(2).setCellStyle(setBorder);
				row.createCell(3).setCellValue(menterpriseInfoEntity.getTel());
				row.getCell(3).setCellStyle(setBorder);
				row.createCell(4).setCellValue(menterpriseInfoEntity.getRegisteredAddress());
				row.getCell(4).setCellStyle(setBorder);
				row.createCell(5).setCellValue(menterpriseInfoEntity.getCountryCode());
				row.getCell(5).setCellStyle(setBorder);
				row.createCell(6).setCellValue(menterpriseInfoEntity.getEnterpriseCodeType());
				row.getCell(6).setCellStyle(setBorder);
				row.createCell(7).setCellValue(menterpriseInfoEntity.getEnterpriseCode());
				row.getCell(7).setCellStyle(setBorder);
				row.createCell(8).setCellValue(menterpriseInfoEntity.getAeoEnterpriseCode());
				row.getCell(8).setCellStyle(setBorder);
			}
			/*********分箱明细sheet*********/
			workbook.write(os);
			workbook.close();
			String filename_enc = UriUtils.encode("分票分箱明细报表", "UTF-8");
			response = ResponseEntity.ok()
				.contentType(MediaType.parseMediaType("application/octet-stream"))
				.header("Access-Control-Expose-Headers","Content-Disposition")
				.header("Content-Disposition","attachment; filename*=UTF-8''" + filename_enc+".xls")
				.body(os.toByteArray());
			return response;
		} catch (Exception e) {
			e.printStackTrace();
			throw new CommonBusinessException(e.getMessage().toString());
		}
	}
	
	/**
	 * 箱票明细报表导出
	 * @param list 向单元格插入数据
	 * @return
	*/
	private ResponseEntity<byte[]> packingListExcel(
			List<MPackingDetailsInfoEntity> list,
			List<MEnterpriseInfoEntity> listEnterprise,
			Locale locale){
		messageBean.setLocale(null,null,locale);
		ResponseEntity<byte[]> response = null;
		try {
			// 读取模板
	        ClassPathResource classPathResource = new ClassPathResource("template/packingDetail/"+"packingListTemp.xlsx");
	        InputStream inputStream= classPathResource.getInputStream();
			//创建Excel对象
			ByteArrayOutputStream os = new ByteArrayOutputStream();
			HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
			// 明细数据样式
			HSSFCellStyle setBorder = workbook.createCellStyle();
			// 设置边框
			setBorder.setBorderTop(BorderStyle.THIN);
			setBorder.setBorderBottom(BorderStyle.THIN);
			setBorder.setBorderLeft(BorderStyle.THIN);
			setBorder.setBorderRight(BorderStyle.THIN);

			/**
			 * 根据 箱号 将list中的所有不重复的箱号放到一个集合中
			 */
			List<String> caseNumberList =  new ArrayList<>();
			caseNumberList.add(list.get(0).getCaseNumber());
            for (MPackingDetailsInfoEntity item:list) {
                if (!(caseNumberList.contains(item.getCaseNumber()))){
                    caseNumberList.add(item.getCaseNumber());
                }
            }

			/**
			 * 根据list的数量复制原来的模板
			 */
			for (int i = 0 ;i<caseNumberList.size();i++){
				if (i==0){
					workbook.setSheetName(0,"PL_"+caseNumberList.get(i));
					workbook.setSheetName(1,"CI_"+caseNumberList.get(i));
				}else {
					HSSFSheet PLNew= workbook.createSheet("PL_"+caseNumberList.get(i));
					HSSFSheet CLNew= workbook.createSheet("CI_"+caseNumberList.get(i));
					ExcelCopySheetUtil.copySheets(PLNew,workbook.getSheetAt(0));
					ExcelCopySheetUtil.copySheets(CLNew,workbook.getSheetAt(1));
				}

			}

			/**
			 * 循环写出每一个list
			 */
            int num=-1;
			for (int i = 0;i<caseNumberList.size();i++){
			    //从list中获取和caseNumberList 里面的箱号一样的 新的list  用来进行excel的编写
                List<MPackingDetailsInfoEntity> resultList = new ArrayList<>();
                for (MPackingDetailsInfoEntity item:list) {
                    if (caseNumberList.get(i).equals(item.getCaseNumber())){
                        resultList.add(item);
                    }
                }
                //求和  将resultList里面的 需要的数据 求和
                BigDecimal packageTotal=new BigDecimal("0");
                BigDecimal quantity=new BigDecimal("0");
                BigDecimal nWeight=new BigDecimal("0");
                BigDecimal gWeight=new BigDecimal("0");
                for (int y=0;y<resultList.size();y++){
					packageTotal=packageTotal.add(new BigDecimal(resultList.get(y).getNumberOfPackages()));
					quantity=quantity.add(new BigDecimal(resultList.get(y).getNum()));
					nWeight=nWeight.add(new BigDecimal(resultList.get(y).getNetWeight()));
					gWeight=gWeight.add(new BigDecimal(resultList.get(y).getGrossWeight()));
                }
                /*********箱单发票sheet01 PL  start*********/
                num=num+1;
                HSSFSheet sheet = workbook.getSheetAt(num);
                Map<String,String> fromToInfo = getFromTo(listEnterprise);
                // Shipper/exporter
                HSSFRow exporter = sheet.getRow(3);
                exporter.getCell(1).setCellValue(fromToInfo.get("fromName"));
                // Shipper/exporter  address
                HSSFRow exporterAddress = sheet.getRow(4);
                exporterAddress.getCell(1).setCellValue(fromToInfo.get("fromAddOne")
                		+","+fromToInfo.get("fromAddTwo")
                		+","+fromToInfo.get("fromAddThree"));
                // Consignee/importer
                HSSFRow importer = sheet.getRow(6);
                importer.getCell(1).setCellValue(fromToInfo.get("toName"));
                // Consignee/importer  address
                HSSFRow importerAddress0 = sheet.getRow(7);
                importerAddress0.getCell(1).setCellValue(fromToInfo.get("toAddOne"));
                HSSFRow importerAddress1 = sheet.getRow(8);
                importerAddress1.getCell(1).setCellValue(fromToInfo.get("toAddTwo"));
                //DATE:   Invoice No
                HSSFRow date = sheet.getRow(9);
				date.getCell(1).setCellValue(resultList.get(0).getContractNo());
				date.getCell(4).setCellValue(changeDate2Display(new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()),"yyyy/MM/dd"));
                //Contract No.
                HSSFRow contractNo = sheet.getRow(10);
                contractNo.getCell(1).setCellValue("BWHR180830");
                // to
                HSSFRow to = sheet.getRow(11);
				to.getCell(1).setCellValue(list.get(0).getPort());
				to.getCell(4).setCellValue(list.get(0).getDestinationPort());
				sheet.shiftRows(13,24,resultList.size());
                //列表
                int totalRowNumber=0;
                for(int z = 0;z<resultList.size();z++) {

                    HSSFRow row = sheet.createRow(z+13);
                    MPackingDetailsInfoEntity packinigDetail = resultList.get(z);
                    //Container NO
                    row.createCell(0).setCellValue(packinigDetail.getCaseNumber());
                    row.getCell(0).setCellStyle(setBorder);
                    //Description of Goods
                    row.createCell(1).setCellValue(packinigDetail.getMaterialNameEn());
                    row.getCell(1).setCellStyle(setBorder);
                    //Material Number
                    row.createCell(2).setCellValue(packinigDetail.getMaterialCode());
                    row.getCell(2).setCellStyle(setBorder);
                    //Packages
                    row.createCell(3).setCellType(CellType.NUMERIC);
                    row.getCell(3).setCellValue(Double.parseDouble(packinigDetail.getNumberOfPackages().toString()));
                    row.getCell(3).setCellStyle(setBorder);
                    //Quantity
                    row.createCell(4).setCellType(CellType.NUMERIC);
                    row.getCell(4).setCellValue(Double.parseDouble(packinigDetail.getNum().toString()));
                    row.getCell(4).setCellStyle(setBorder);
                    //N .Weight
                    row.createCell(5).setCellType(CellType.NUMERIC);
                    row.getCell(5).setCellValue(Double.parseDouble(packinigDetail.getNetWeight().toString()));
                    row.getCell(5).setCellStyle(setBorder);
                    //G.Weight
                    row.createCell(6).setCellType(CellType.NUMERIC);
                    row.getCell(6).setCellValue(Double.parseDouble(packinigDetail.getGrossWeight().toString()));
                    row.getCell(6).setCellStyle(setBorder);

                    if (z==resultList.size()-1){
                        totalRowNumber=z+13+1;
                    }
                }
                //total
                HSSFRow total = sheet.getRow(totalRowNumber);
                total.getCell(3).setCellType(CellType.NUMERIC);
                total.getCell(3).setCellValue(Double.parseDouble(packageTotal.toString()));
                total.getCell(3).setCellStyle(setBorder);
                total.getCell(4).setCellType(CellType.NUMERIC);
                total.getCell(4).setCellValue(Double.parseDouble(quantity.toString()));
                total.getCell(4).setCellStyle(setBorder);
                total.getCell(5).setCellType(CellType.NUMERIC);
                total.getCell(5).setCellValue(Double.parseDouble(nWeight.toString()));
                total.getCell(5).setCellStyle(setBorder);
                total.getCell(6).setCellType(CellType.NUMERIC);
                total.getCell(6).setCellValue(Double.parseDouble(gWeight.toString()));
                total.getCell(6).setCellStyle(setBorder);
                //totalALL
                HSSFRow totalALL = sheet.getRow(totalRowNumber+3);
                if(packageTotal != null && packageTotal.toString().length()>0) {
                	totalALL.getCell(1).setCellValue(packageTotal.toString()+" PACKAGES");
                }else {
                	totalALL.getCell(1).setCellValue(packageTotal.toString());
                }
                //containerNo
                HSSFRow containerNo = sheet.getRow(totalRowNumber+4);
                containerNo.getCell(1).setCellValue(resultList.get(0).getCaseNumber());
                /*********箱单发票sheet01 PL  end*********/

                /*********箱单发票sheet02 CL  start*********/
                num=num+1;
                HSSFSheet nextSheet = workbook.getSheetAt(num);
				// Shipper/exporter
				HSSFRow nextExporter = nextSheet.getRow(3);
				nextExporter.getCell(1).setCellValue(fromToInfo.get("fromName"));
				// Shipper/exporter  address
				HSSFRow nextExporterAddress = nextSheet.getRow(4);
				nextExporterAddress.getCell(1).setCellValue(fromToInfo.get("fromAddOne")
                		+","+fromToInfo.get("fromAddTwo")
                		+","+fromToInfo.get("fromAddThree"));
				// Consignee/importer
				HSSFRow nextImporter = nextSheet.getRow(6);
				nextImporter.getCell(1).setCellValue(fromToInfo.get("toName"));
				// Consignee/importer  address
				HSSFRow nextImporterAddress0 = nextSheet.getRow(7);
				nextImporterAddress0.getCell(1).setCellValue(fromToInfo.get("toAddOne"));
				HSSFRow nextImporterAddress1 = nextSheet.getRow(8);
				nextImporterAddress1.getCell(1).setCellValue(fromToInfo.get("toAddTwo"));
				//DATE:
				HSSFRow nextDate = nextSheet.getRow(9);
				nextDate.getCell(1).setCellValue(resultList.get(0).getContractNo());
				nextDate.getCell(4).setCellValue(changeDate2Display(new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()),"yyyy/MM/dd"));
				//Contract No.
				HSSFRow nextContractNo = nextSheet.getRow(10);
				nextContractNo.getCell(1).setCellValue("BWHR180830");
				// to
				HSSFRow nextTo = nextSheet.getRow(11);
				nextTo.getCell(1).setCellValue(list.get(0).getPort());
				nextTo.getCell(4).setCellValue(list.get(0).getDestinationPort());
				nextSheet.shiftRows(13,27,resultList.size());
				//列表
				int nextTotalRowNumber=0;
				for(int z = 0;z<resultList.size();z++) {

					HSSFRow row = nextSheet.createRow(z+13);
					MPackingDetailsInfoEntity packinigDetail = resultList.get(z);
					//Description of Goods
					row.createCell(1).setCellValue(packinigDetail.getMaterialNameEn());
					row.getCell(1).setCellStyle(setBorder);
					//Material Number
					row.createCell(2).setCellValue(packinigDetail.getMaterialCode());
					row.getCell(2).setCellStyle(setBorder);
					//Quantity
					row.createCell(3).setCellType(CellType.NUMERIC);
					row.getCell(3).setCellValue(Double.parseDouble(packinigDetail.getNum().toString()));
					row.getCell(3).setCellStyle(setBorder);
					//Unit price
					row.createCell(4).setCellType(CellType.NUMERIC);
					row.getCell(4).setCellValue(Double.parseDouble(packinigDetail.getMaterialPrice().toString()));
					row.getCell(4).setCellStyle(setBorder);
					//Total Amount
					row.createCell(5).setCellType(CellType.NUMERIC);
					row.getCell(5).setCellValue(Double.parseDouble(packinigDetail.getTotalValue().toString()));
					row.getCell(5).setCellStyle(setBorder);

					if (z==resultList.size()-1){
						nextTotalRowNumber=z+13+1;
					}
				}
				//Ocean freight
				HSSFRow nextOceanfreight = nextSheet.getRow(nextTotalRowNumber);
				nextOceanfreight.getCell(5).setCellStyle(setBorder);
				//Insurance
				HSSFRow nextInsurance = nextSheet.getRow(nextTotalRowNumber+1);
				nextInsurance.getCell(5).setCellStyle(setBorder);
				//total
				HSSFRow nextTotal = nextSheet.getRow(nextTotalRowNumber+2);
				nextTotal.getCell(5).setCellStyle(setBorder);
				//totalALL
				HSSFRow nextTotalALL = nextSheet.getRow(nextTotalRowNumber+5);
				if(packageTotal != null && packageTotal.toString().length()>0) {
					nextTotalALL.getCell(1).setCellValue(packageTotal.toString()+" PACKAGES");
				}else {
					nextTotalALL.getCell(1).setCellValue(packageTotal.toString());
				}
				//SAY
				HSSFRow say = nextSheet.getRow(nextTotalRowNumber+6);
				say.getCell(1).setCellValue("CNY THREE HUNDRED AND TWENTY-THREE THOUSAND AND SIX HUNDRED AND THIRTY-SIX ONLY ");
                /*********箱单发票sheet02 CL  end*********/
            }


			workbook.write(os);
			workbook.close();
			String filename_enc = UriUtils.encode("箱单箱票明细报表", "UTF-8");
			response = ResponseEntity.ok()
				.contentType(MediaType.parseMediaType("application/octet-stream"))
				.header("Access-Control-Expose-Headers","Content-Disposition")
				.header("Content-Disposition","attachment; filename*=UTF-8''" + filename_enc+".xls")
				.body(os.toByteArray());
			return response;
		} catch (Exception e) {
            e.printStackTrace();
			throw new CommonBusinessException(e.getMessage().toString());
		}
	}

	/**
	 * 铁路随车单据报表导出
	 * @param list 向单元格插入数据
	 * @return
	 */
	private ResponseEntity<byte[]> billOfLadingSampleReportExcel(
			List<MPackingDetailsInfoEntity> list,
			List<MEnterpriseInfoEntity> listEnterprise,
			Locale locale){
		messageBean.setLocale(null,null,locale);
		ResponseEntity<byte[]> response = null;
		try {
			// 读取模板
			ClassPathResource classPathResource = new ClassPathResource("template/packingDetail/"+"billOfLadingSample.xlsx");
			InputStream inputStream= classPathResource.getInputStream();
			//创建Excel对象
			ByteArrayOutputStream os = new ByteArrayOutputStream();
			HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
			// 明细数据样式
			HSSFCellStyle setBorder = workbook.createCellStyle();
			// 设置边框
			setBorder.setBorderTop(BorderStyle.THIN);
			setBorder.setBorderBottom(BorderStyle.THIN);
			setBorder.setBorderLeft(BorderStyle.THIN);
			setBorder.setBorderRight(BorderStyle.THIN);

			/**
			 * 根据 箱号 将list中的所有不重复的箱号放到一个集合中
			 */
			List<String> caseNumberList =  new ArrayList<>();
			caseNumberList.add(list.get(0).getCaseNumber());
			for (MPackingDetailsInfoEntity item:list) {
				if (!(caseNumberList.contains(item.getCaseNumber()))){
					caseNumberList.add(item.getCaseNumber());
				}
			}

			/**
			 * 根据list的数量复制原来的模板
			 */
			for (int i = 0 ;i<caseNumberList.size();i++){
				if (i==0){
					workbook.setSheetName(0,caseNumberList.get(i)==null?"NoCaseNumber":caseNumberList.get(i));
				}else {
					HSSFSheet PLNew= workbook.createSheet(caseNumberList.get(i));
					ExcelCopySheetUtil.copySheets(PLNew,workbook.getSheetAt(0));
				}
			}
			/**
			 * 循环写出每一个list
			 */
			for (int i = 0;i<caseNumberList.size();i++){
				//从list中获取和caseNumberList 里面的箱号一样的 新的list  用来进行excel的编写
				List<MPackingDetailsInfoEntity> resultList = new ArrayList<>();
				for (MPackingDetailsInfoEntity item:list) {
					if (item.getCaseNumber().equals(caseNumberList.get(i))){
						resultList.add(item);
					}
				}
				//求和  将resultList里面的 需要的数据 求和
				BigDecimal packageTotal=new BigDecimal("0");
				BigDecimal quantity=new BigDecimal("0");
				BigDecimal nWeight=new BigDecimal("0");
				BigDecimal gWeight=new BigDecimal("0");
				BigDecimal Amount =new BigDecimal(0);
				for (int y=0;y<resultList.size();y++){
					packageTotal=packageTotal.add(new BigDecimal(resultList.get(y).getNumberOfPackages()));
					quantity=quantity.add(new BigDecimal(resultList.get(y).getNum()));
					nWeight=nWeight.add(new BigDecimal(resultList.get(y).getNetWeight()));
					gWeight=gWeight.add(new BigDecimal(resultList.get(y).getGrossWeight()));
					Amount=Amount.add(new BigDecimal(resultList.get(y).getTotalValue()));
				}
				/*********箱单发票sheet01 PL  start*********/
				HSSFSheet sheet = workbook.getSheetAt(i);

				//合同号
				HSSFRow contractNo = sheet.getRow(2);
				contractNo.getCell(2).setCellValue(resultList.get(0).getContractNo());

				Map<String,String> fromToInfo = getFromTo(listEnterprise);
				//卖方 地址  （发货方）
				HSSFRow exporter = sheet.getRow(7);
				exporter.getCell(3).setCellValue(fromToInfo.get("fromName")
						+","+fromToInfo.get("fromAddOne")
						+","+fromToInfo.get("fromAddTwo")
						+","+fromToInfo.get("fromAddThree"));
				//买方 地址  （收货方）
				HSSFRow importer = sheet.getRow(7);
				importer.getCell(6).setCellValue(fromToInfo.get("toName")
						+","+fromToInfo.get("toAddOne")
						+","+fromToInfo.get("toAddTwo"));
				//CIP
				HSSFRow CIP = sheet.getRow(11);
				CIP.getCell(3).setCellValue("CIP");
				//to
				CIP.getCell(8).setCellValue("Russia");
				//柜号 == 箱号  目的地站名
				HSSFRow caseNumber =sheet.getRow(12);
				caseNumber.getCell(3).setCellValue(caseNumberList.get(i));
				caseNumber.getCell(8).setCellValue(resultList.get(0).getDestinationPort());

				sheet.shiftRows(15,24,resultList.size());
				//列表
				int totalRowNumber=0;
				for(int z = 0;z<resultList.size();z++) {

					HSSFRow row = sheet.createRow(z+15);
					MPackingDetailsInfoEntity packinigDetail = resultList.get(z);
					//no
					row.createCell(1).setCellValue((z+1));
					row.getCell(1).setCellStyle(setBorder);
					//Material Number
					row.createCell(2).setCellValue(packinigDetail.getHsNo());
					row.getCell(2).setCellStyle(setBorder);
					//货物出产国
					row.createCell(3).setCellValue("CHINA");
					row.getCell(3).setCellStyle(setBorder);
					//Description of Goods
					row.createCell(4).setCellValue(packinigDetail.getMaterialNameEn());
					row.getCell(4).setCellStyle(setBorder);
					//Quantity
					row.createCell(5).setCellType(CellType.NUMERIC);
					row.getCell(5).setCellValue(Double.parseDouble(packinigDetail.getNum()));
					row.getCell(5).setCellStyle(setBorder);
					//Packages
					row.createCell(6).setCellType(CellType.NUMERIC);
					row.getCell(6).setCellValue(Double.parseDouble(packinigDetail.getNumberOfPackages()));
					row.getCell(6).setCellStyle(setBorder);
					//N .Weight
					row.createCell(7).setCellType(CellType.NUMERIC);
					row.getCell(7).setCellValue(Double.parseDouble(packinigDetail.getNetWeight()));
					row.getCell(7).setCellStyle(setBorder);
					//G.Weight
					row.createCell(8).setCellType(CellType.NUMERIC);
					row.getCell(8).setCellValue(Double.parseDouble(packinigDetail.getGrossWeight()));
					row.getCell(8).setCellStyle(setBorder);
					//单价
					row.createCell(9).setCellType(CellType.NUMERIC);
					row.getCell(9).setCellValue(Double.parseDouble(packinigDetail.getMaterialPrice()));
					row.getCell(9).setCellStyle(setBorder);
					//总价
					row.createCell(10).setCellType(CellType.NUMERIC);
					row.getCell(10).setCellValue(Double.parseDouble(packinigDetail.getTotalValue()));
					row.getCell(10).setCellStyle(setBorder);

					if (z==resultList.size()-1){
						totalRowNumber=z+15+1;
					}
				}
				//total
				HSSFRow total = sheet.getRow(totalRowNumber);
				total.createCell(5).setCellType(CellType.NUMERIC);
				total.getCell(5).setCellValue(Double.parseDouble(quantity.toString()));
				total.getCell(5).setCellStyle(setBorder);
				total.createCell(6).setCellType(CellType.NUMERIC);
				total.getCell(6).setCellValue(Double.parseDouble(packageTotal.toString()));
				total.getCell(6).setCellStyle(setBorder);
				total.createCell(7).setCellType(CellType.NUMERIC);
				total.getCell(7).setCellValue(Double.parseDouble(nWeight.toString()));
				total.getCell(7).setCellStyle(setBorder);
				total.createCell(8).setCellType(CellType.NUMERIC);
				total.getCell(8).setCellValue(Double.parseDouble(gWeight.toString()));
				total.getCell(8).setCellStyle(setBorder);
				total.createCell(10).setCellType(CellType.NUMERIC);
				total.getCell(10).setCellValue(Double.parseDouble(Amount.toString()));
				total.getCell(10).setCellStyle(setBorder);
				/*********箱单发票sheet01 PL  end*********/

			}
			workbook.write(os);
			workbook.close();
			String filename_enc = UriUtils.encode("铁路随车单据", "UTF-8");
			response = ResponseEntity.ok()
					.contentType(MediaType.parseMediaType("application/octet-stream"))
					.header("Access-Control-Expose-Headers","Content-Disposition")
					.header("Content-Disposition","attachment; filename*=UTF-8''" + filename_enc+".xls")
					.body(os.toByteArray());
			return response;
		} catch (Exception e) {
			e.printStackTrace();
			throw new CommonBusinessException(e.getMessage().toString());
		}
	}

	/**
	 *保单样本报表
	 * @param list 向单元格插入数据
	 * @return
	 */
	private ResponseEntity<byte[]> insuranceReport(
			List<MPackingDetailsInfoEntity> list,
			List<MEnterpriseInfoEntity> listEnterprise,
			Locale locale){
		messageBean.setLocale(null,null,locale);
		ResponseEntity<byte[]> response = null;
		try {
			// 读取模板
			ClassPathResource classPathResource = new ClassPathResource("template/packingDetail/"+"insuranceTemp.xls");
			InputStream inputStream= classPathResource.getInputStream();
			//创建Excel对象
			ByteArrayOutputStream os = new ByteArrayOutputStream();
			HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
			// 明细数据样式
			HSSFCellStyle setBorder = workbook.createCellStyle();
			// 设置边框
			setBorder.setBorderTop(BorderStyle.THIN);
			setBorder.setBorderBottom(BorderStyle.THIN);
			setBorder.setBorderLeft(BorderStyle.THIN);
			setBorder.setBorderRight(BorderStyle.THIN);
			
			//求和  将list里面的 需要的数据 求和
			BigDecimal packageTotal = new BigDecimal("0");
			BigDecimal quantity = new BigDecimal("0");
			BigDecimal nWeight = new BigDecimal("0");
			BigDecimal gWeight = new BigDecimal("0");
			for (int y = 0; y < list.size(); y++) {
				packageTotal = packageTotal.add(new BigDecimal(list.get(y).getNumberOfPackages()));
				quantity = quantity.add(new BigDecimal(list.get(y).getNum()));
				nWeight = nWeight.add(new BigDecimal(list.get(y).getNetWeight()));
				gWeight = gWeight.add(new BigDecimal(list.get(y).getGrossWeight()));
			}
			/*********箱单发票sheet01 PL  start*********/
			HSSFSheet sheet = workbook.getSheetAt(0);
			Map<String,String> fromToInfo = getFromTo(listEnterprise);
			// 发货方
			// Shipper/exporter
			HSSFRow exporter = sheet.getRow(3);
			exporter.createCell(1).setCellValue(fromToInfo.get("fromName"));
			// Shipper/exporter  address
			HSSFRow exporterAddress = sheet.getRow(4);
			exporterAddress.createCell(1).setCellValue(fromToInfo.get("fromAddOne")
					+","+fromToInfo.get("fromAddTwo")
					+","+fromToInfo.get("fromAddThree"));
			// 收货方
			// Consignee/importer
			HSSFRow importer = sheet.getRow(6);
			importer.createCell(1).setCellValue(fromToInfo.get("toName"));
			// Consignee/importer  address
			HSSFRow importerAddress0 = sheet.getRow(7);
			importerAddress0.createCell(1).setCellValue(fromToInfo.get("toAddOne"));
			HSSFRow importerAddress1 = sheet.createRow(8);
			importerAddress1.createCell(1).setCellValue(fromToInfo.get("toAddTwo"));
//			//DATE:
//			HSSFRow date = sheet.getRow(9);
//			date.getCell(4).setCellValue(changeDate2Display(new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()), "yyyy/MM/dd"));
			//Contract No.
			HSSFRow contractNo = sheet.getRow(10);
			contractNo.createCell(1).setCellValue(list.get(0).getContractNo());
			// to
			HSSFRow to = sheet.getRow(11);
			to.createCell(1).setCellValue(list.get(0).getPort());
			to.createCell(4).setCellValue(list.get(0).getDestinationPort());
//				sheet.shiftRows(13,24,resultList.size());
			sheet.shiftRows(13, 17, list.size());
			//列表
			int totalRowNumber = 0;
			for (int z = 0; z < list.size(); z++) {

				HSSFRow row = sheet.createRow(z + 13);
				MPackingDetailsInfoEntity packinigDetail = list.get(z);
				
				//Description of Goods
				CellRangeAddress cra = new CellRangeAddress(z + 13, z + 13, 1, 2);
				RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet);
				sheet.addMergedRegion(cra);
				row.createCell(1).setCellValue(packinigDetail.getMaterialNameEn());
				row.getCell(1).setCellStyle(setBorder);
				//Total Amount（CNY)
				CellRangeAddress craCny = new CellRangeAddress(z + 13, z + 13, 3, 4);
				RegionUtil.setBorderTop(BorderStyle.THIN, craCny, sheet);
				RegionUtil.setBorderRight(BorderStyle.THIN, craCny, sheet);
				sheet.addMergedRegion(craCny);
				row.createCell(3).setCellType(CellType.NUMERIC);
				row.getCell(3).setCellValue(Double.parseDouble(packinigDetail.getTotalValue()));
				row.getCell(3).setCellStyle(setBorder);
				if (z == list.size() - 1) {
					totalRowNumber = z + 13 + 1;
				}
			}
			//total
			HSSFRow total = sheet.getRow(totalRowNumber+4);
			total.createCell(1).setCellValue(packageTotal.toString()+"PACKAGES");
			/*********箱单发票sheet01 PL  end*********/

			workbook.write(os);
			workbook.close();
			String filename_enc = UriUtils.encode("保单样本报表", "UTF-8");
			response = ResponseEntity.ok()
					.contentType(MediaType.parseMediaType("application/octet-stream"))
					.header("Access-Control-Expose-Headers","Content-Disposition")
					.header("Content-Disposition","attachment; filename*=UTF-8''" + filename_enc+".xls")
					.body(os.toByteArray());
			return response;
		} catch (Exception e) {
			e.printStackTrace();
			throw new CommonBusinessException(e.getMessage().toString());
		}
	}
	
	/**
	 * 附件明细报表导出
	 * @param list 向单元格插入数据
	 * @return
	*/
	private ResponseEntity<byte[]> attachmentListExcel(
			List<MPackingDetailsInfoEntity> list,
			Locale locale){
		messageBean.setLocale(null,null,locale);
		ResponseEntity<byte[]> response = null;
		try {
			// 读取模板
	        ClassPathResource classPathResource = new ClassPathResource("template/packingDetail/"+"attachmentOrderPlan.xlsx");
	        InputStream inputStream= classPathResource.getInputStream();
			//创建Excel对象
			ByteArrayOutputStream os = new ByteArrayOutputStream();
			HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
			// 明细数据样式
			HSSFCellStyle setBorder = workbook.createCellStyle();
			// 设置边框
			setBorder.setBorderTop(BorderStyle.THIN);
			setBorder.setBorderBottom(BorderStyle.THIN);
			setBorder.setBorderLeft(BorderStyle.THIN);
			setBorder.setBorderRight(BorderStyle.THIN);
			
			HSSFSheet firstSheet = workbook.getSheetAt(0);
			
			for(int i = 0;i<list.size();i++) {
				
				firstSheet.shiftRows(i+2, i+7, 1);
				HSSFRow row = firstSheet.createRow(i+2);
				MPackingDetailsInfoEntity packinigDetail = list.get(i);
				row.createCell(1).setCellValue(i+1);
				row.getCell(1).setCellStyle(setBorder);
				row.createCell(2).setCellValue(packinigDetail.getMaterialCode());
				row.getCell(2).setCellStyle(setBorder);
				String materName = packinigDetail.getMaterialNameCn() ;
				row.createCell(3).setCellValue(materName);
				row.getCell(3).setCellStyle(setBorder);
				row.createCell(4).setCellType(CellType.NUMERIC);
				row.createCell(4).setCellValue(Double.parseDouble(packinigDetail.getNum()));
				row.getCell(4).setCellStyle(setBorder);
				row.createCell(5).setCellType(CellType.NUMERIC);
				row.createCell(5).setCellValue(Double.parseDouble(packinigDetail.getMaterialPrice()));
				row.getCell(5).setCellStyle(setBorder);
				row.createCell(6).setCellType(CellType.NUMERIC);
				row.createCell(6).setCellValue(Double.parseDouble(packinigDetail.getTotalValue()));
				row.getCell(6).setCellStyle(setBorder);
				row.createCell(7).setCellValue(packinigDetail.getSupplierName());
				row.getCell(7).setCellStyle(setBorder);
				row.createCell(8).setCellType(CellType.NUMERIC);
				row.createCell(8).setCellValue(Double.parseDouble(packinigDetail.getAgencyFee()));
				row.getCell(8).setCellStyle(setBorder);
				row.createCell(9).setCellValue(packinigDetail.getRemark());
				row.getCell(9).setCellStyle(setBorder);
			}
			workbook.write(os);
			workbook.close();
			String filename_enc = UriUtils.encode("附件订单计划", "UTF-8");
			response = ResponseEntity.ok()
				.contentType(MediaType.parseMediaType("application/octet-stream"))
				.header("Access-Control-Expose-Headers","Content-Disposition")
				.header("Content-Disposition","attachment; filename*=UTF-8''" + filename_enc+".xls")
				.body(os.toByteArray());
			return response;
		} catch (Exception e) {
			throw new CommonBusinessException(e.getMessage().toString());
		}
	}
	
	/**
	 *提单样本报表导出
	 * @param list 向单元格插入数据
	 * @return
	*/
	private ResponseEntity<byte[]> bookingListReport(
			List<MPackingDetailsInfoEntity> list,
			List<MEnterpriseInfoEntity> listEnterprise,
			Locale locale){
		messageBean.setLocale(null,null,locale);
		ResponseEntity<byte[]> response = null;
		try {
			// 读取模板
	        ClassPathResource classPathResource = new ClassPathResource("template/packingDetail/"+"bookingTemp.xls");
	        InputStream inputStream= classPathResource.getInputStream();
			//创建Excel对象
			ByteArrayOutputStream os = new ByteArrayOutputStream();
			HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
			// 明细数据样式
			HSSFCellStyle setBorder = workbook.createCellStyle();
			// 设置边框
			setBorder.setBorderTop(BorderStyle.THIN);
			setBorder.setBorderBottom(BorderStyle.THIN);
			setBorder.setBorderLeft(BorderStyle.THIN);
			setBorder.setBorderRight(BorderStyle.THIN);
			
			HSSFSheet firstSheet = workbook.getSheetAt(0);
			
			Map<String,String> fromToInfo = getFromTo(listEnterprise);
			// 发货方
			HSSFRow fromNameRow = firstSheet.getRow(1);
			fromNameRow.createCell(0).setCellValue(fromToInfo.get("fromName"));
			HSSFRow fromAddOneRow = firstSheet.createRow(2);
			fromAddOneRow.createCell(0).setCellValue(fromToInfo.get("fromAddOne"));
			HSSFRow fromTwoRow = firstSheet.createRow(3);
			fromTwoRow.createCell(0).setCellValue(fromToInfo.get("fromAddTwo"));
			HSSFRow fromThreeRow = firstSheet.getRow(4);
			fromThreeRow.createCell(0).setCellValue(fromToInfo.get("fromAddThree"));
			// 收货方
			HSSFRow toNameRow = firstSheet.getRow(7);
			toNameRow.createCell(0).setCellValue(fromToInfo.get("toName"));
			HSSFRow toAddOneRow = firstSheet.createRow(8);
			toAddOneRow.createCell(0).setCellValue(fromToInfo.get("toAddOne"));
			HSSFRow toTwoRow = firstSheet.createRow(9);
			toTwoRow.createCell(0).setCellValue(fromToInfo.get("toAddTwo"));
			HSSFRow toThreeRow = firstSheet.getRow(10);
			toThreeRow.createCell(0).setCellValue(fromToInfo.get("toAddThree"));
		    // 日期
			HSSFRow dateRow = firstSheet.getRow(17);
			dateRow.createCell(9).setCellValue(changeDate2Display(new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()), "yyyy/MM/dd"));
			// 港口设定
			HSSFRow portRow = firstSheet.createRow(19);
			portRow.createCell(3).setCellValue(list.get(0).getPort()+"CHINA");
			HSSFRow dportRow = firstSheet.createRow(21);
			dportRow.createCell(0).setCellValue(list.get(0).getDestinationPort());
			dportRow.createCell(3).setCellValue(list.get(0).getPort()+"CHINA");
			dportRow.createCell(8).setCellValue(list.get(0).getDestinationPort());
			// 明细主数据
			HSSFRow detailRow = firstSheet.createRow(24);
			detailRow.createCell(2).setCellType(CellType.NUMERIC);
			detailRow.getCell(2).setCellValue(Double.parseDouble(list.get(0).getNumberOfPackages()));
			detailRow.createCell(9).setCellType(CellType.NUMERIC);
			detailRow.getCell(9).setCellValue(Double.parseDouble(list.get(0).getGrossWeight()));
			detailRow.createCell(10).setCellType(CellType.NUMERIC);
			detailRow.getCell(10).setCellValue(Double.parseDouble(list.get(0).getSize()));
			for(int i = 0;i<list.size();i++) {
				firstSheet.shiftRows(i+25, i+37, 1);
				HSSFRow row = firstSheet.createRow(i+25);
				MPackingDetailsInfoEntity packinigDetail = list.get(i);
				row.createCell(4).setCellValue(packinigDetail.getMaterialNameEn());
			}
			HSSFRow footerRow = firstSheet.getRow(list.size()+26);
			footerRow.getCell(7).setCellValue(list.get(0).getContainerSize());
			
			workbook.write(os);
			workbook.close();
			String filename_enc = UriUtils.encode("提单样本报表", "UTF-8");
			response = ResponseEntity.ok()
				.contentType(MediaType.parseMediaType("application/octet-stream"))
				.header("Access-Control-Expose-Headers","Content-Disposition")
				.header("Content-Disposition","attachment; filename*=UTF-8''" + filename_enc+".xls")
				.body(os.toByteArray());
			return response;
		} catch (Exception e) {
			throw new CommonBusinessException(e.getMessage().toString());
		}
	}
	/**
	 * 收货发货方
	 */
	private static Map<String,String> getFromTo(List<MEnterpriseInfoEntity> listEnter) {
		Map<String,String> fromToMap = new HashMap<String,String>();
		fromToMap.put("fromName", "");
		fromToMap.put("fromAddOne", "");
		fromToMap.put("fromAddTwo", "");
		fromToMap.put("fromAddThree", "");
		fromToMap.put("toName", "");
		fromToMap.put("toAddOne", "");
		fromToMap.put("toAddTwo", "");
		fromToMap.put("toAddThree", "");
		for(int e=0;e<listEnter.size();e++) {
			MEnterpriseInfoEntity enterEntity = listEnter.get(e);
			if("1".equals(enterEntity.getType().toString())) {
				fromToMap.put("fromName", enterEntity.getNameEn());
				String[] from = enterEntity.getAddress().split(";");
				fromToMap.put("fromAddOne", from[0]);
				fromToMap.put("fromAddTwo", from[1]);
				fromToMap.put("fromAddThree", from[2]);
			}else if("2".equals(enterEntity.getType().toString())) {
				fromToMap.put("toName", enterEntity.getNameEn());
				String[] from = enterEntity.getAddress().split(";");
				fromToMap.put("toAddOne", from[0]);
				fromToMap.put("toAddTwo", from[1]);
				fromToMap.put("toAddThree", enterEntity.getTel());
			}
		}
		return fromToMap;
	}
	/**
	 * 日期转换
	 * @return
	 */
	private static String changeDate2Display(String date,String patern) {
		try {
			if(StringUtil.isEmpty(date)) {return "";}
			Date time = new SimpleDateFormat("yyyyMMddHHmmss").parse(date);
	    	SimpleDateFormat df = new SimpleDateFormat(patern);//设置日期格式
	        String dd = df.format(time);// new Date()为获取当前系统时间
	        return dd;
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}

	}
}
